Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Public Class clsUsuario
    Public Function objPerCodigo(ByVal Usuario As String, ByVal clave As String, ByVal MyTrans As SqlTransaction, ByVal Cn As SqlConnection) As SqlDataReader
        Dim sql As String = "Select cPerCodigo from PerUsuario Where " & _
                            " cPerUsuCodigo =   '" & Usuario & "' and " & _
                            " cPerUsuClave= '" & clave & "'"
        Dim MiClase As New clsManejadorDatos
        Return MiClase.objExecuteReader(sql, MyTrans, Cn)
        MiClase = Nothing
    End Function
    Public Function ObjDatUsuario(ByVal PerCodigo As String, ByVal MyTrans As SqlTransaction, ByVal Cn As SqlConnection) As SqlDataReader
        Dim Sql As String = "select Distinct p.cPerCodigo,p.cPerNombre, p.cPerApellido," & _
                     " i.cIntDescripcion,uo.nUniOrgCodigo from Persona p inner join UniOrgPer uo " & _
                     " on uo.cPerCodigo=p.cPercodigo inner join Interface i " & _
                     " on i.nIntCodigo=uo.nUniOrgCodigo inner join Periodo Pd " & _
                     " on uo.nPrdCodigo= pd.nPrdCodigo where nIntClase ='1006' " & _
                     " and p.cPerCodigo = '" & PerCodigo & "' AND ({ fn NOW() } BETWEEN Pd.dPrdIni AND Pd.dPrdFin + 1)"  'AND Pd.nPrdCodigo in(252,113)" 
        Dim MiClase As New clsManejadorDatos
        Return MiClase.objExecuteReader(Sql, MyTrans, Cn)
        MiClase = Nothing
    End Function
    Public Function ObjPerPermios(ByVal PerCodigo As String, ByVal GruTipo As Integer, ByVal ObjTipo As Integer, _
                    ByVal MyTrans As SqlTransaction, ByVal cn As SqlConnection, Optional ByVal ObjCodigo As String = "") As SqlDataReader
        Dim sql As String = "SELECT DISTINCT pg.nObjCodigo " & _
                " FROM dbo.Persona p INNER JOIN " & _
                " dbo.PerUsuGruAcc pg ON p.cPerCodigo = pg.cPerCodigo INNER JOIN " & _
                " dbo.Interface m ON pg.nObjCodigo = m.nIntCodigo " & _
                " WHERE (p.cPerCodigo = '" & PerCodigo & "') AND (pg.nSisGruTipo = " & GruTipo & ") " & _
                " AND (pg.nObjTipo = " & ObjTipo & ") AND (LEFT(m.cIntJerarquia, 2) IN('64','65'))"
        If ObjCodigo <> "" Then
            sql += " AND pg.nObjCodigo in( " & ObjCodigo & ")"
        End If
        Dim MiClase As New clsManejadorDatos
        Return MiClase.objExecuteReader(sql, MyTrans, cn)
        MiClase = Nothing
    End Function
    Public Function objMostAdministrativo(ByVal cPerCodOrApellido As String, ByVal nActividad As String, ByVal MyTrans As SqlTransaction, ByVal cn As SqlConnection, Optional ByVal nByCode As Integer = 0) As SqlDataReader  ' Mostrar Administrativos

        Dim Filtro As String = String.Empty

        If nByCode = 1 Then
            Filtro = "and p.cPerCodigo in( " & cPerCodOrApellido & ")"
        Else
            Filtro = "and p.cPerApellido  Like '%" & cPerCodOrApellido & "%'"
        End If

        Dim sql As String = "Select p.cPerCodigo, (p.cPerNombre + ' ' + p.cPerApellido) AS Nombre," & _
            " i.cIntDescripcion,uo.nUniOrgCodigo from Persona p inner join UniOrgPer uo " & _
            " on uo.cPerCodigo=p.cPercodigo inner join Interface i " & _
            " on i.nIntCodigo=uo.nUniOrgCodigo inner join Periodo Pd " & _
            " on uo.nPrdCodigo= pd.nPrdCodigo where nIntClase ='1006' " & _
            " AND (Pd.nPrdActividad in(" & nActividad & ")) AND " & _
            "({ fn NOW() } BETWEEN Pd.dPrdIni AND Pd.dPrdFin + 1) " & Filtro 'AND Pd.nPrdCodigo in(252,113)"

        Dim MiClase As New clsManejadorDatos
        Return MiClase.objExecuteReader(sql, MyTrans, cn)
        MiClase = Nothing
    End Function
    Public Function objBuscarPersona(ByVal PerNombre As String, ByVal PerTipo As Integer, ByVal MyTrans As SqlTransaction, ByVal Cn As SqlConnection) As SqlDataReader
        Dim Sql As String = "SELECT P.cPerCodigo, P.cPerApellido, P.cPerNombre,P.cPerApellido + ' ' " & _
                            "+ P.cPerNombre As Nombre,  PD.cPerDomDireccion As Direccion  FROM " & _
                            "Persona P LEFT OUTER JOIN  PerDomicilio PD ON P.cPerCodigo = " & _
                            "PD.cPerCodigo WHERE (P.cPerApellido +''+P.cPerNombre LIKE '%" & PerNombre & "%' " & _
                            "OR P.cPerNombre +''+ P.cPerApellido like '%" & PerNombre & "%') AND P.nPerTipo=" & PerTipo & ""
        Dim MiClase As New clsManejadorDatos
        Return MiClase.objExecuteReader(Sql, MyTrans, Cn)
        MiClase = Nothing
    End Function
    Public Function Get_BuscaPersona_By_Relacion(ByVal PerNombre As String, ByVal PerRelacion As Integer, ByVal Pertipo As Integer, _
                                                ByVal MyTrans As SqlTransaction, ByVal Cn As SqlConnection) As SqlDataReader

        Dim SQL As String = "SELECT P.cPerCodigo, P.cPerApellido, P.cPerNombre, P.cPerApellido + ' ' + P.cPerNombre AS Nombre, " & _
                            "PD.cPerDomDireccion AS Direccion, PR.nPerRelTipo " & _
                            "FROM dbo.Persona AS P INNER JOIN " & _
                            "dbo.PerRelacion AS PR ON P.cPerCodigo = PR.cPerCodigo LEFT OUTER JOIN " & _
                            "dbo.PerDomicilio AS PD ON P.cPerCodigo = PD.cPerCodigo " & _
                            "WHERE (P.cPerApellido + '' + P.cPerNombre LIKE '%" & PerNombre & "%' OR " & _
                            "P.cPerNombre + '' + P.cPerApellido LIKE '%" & PerNombre & "%') AND (P.nPerTipo = " & Pertipo & ") " & _
                            "AND (PR.nPerRelEstado = 0) AND (PR.nPerRelTipo = " & PerRelacion & ")"

        Dim MiClase As New clsManejadorDatos
        Return MiClase.objExecuteReader(SQL, MyTrans, Cn)
        MiClase = Nothing

    End Function
    Public Function objPersona(ByVal PerCodigo As String, ByVal MyTrans As SqlTransaction, ByVal Cn As SqlConnection) As SqlDataReader
        Dim sql As String = "SELECT Persona.cPerCodigo, Persona.cPerApellido, Persona.cPerNombre, " & _
                            " Persona.dPerNacimiento, Persona.nPerEstado, Persona.cUbiGeoCodigo, " & _
                            "Persona.nPerTipo FROM Persona WHERE Persona.cPerCodigo = '" & PerCodigo & "' "
        Dim MiClase As New clsManejadorDatos
        Return MiClase.objExecuteReader(sql, MyTrans, Cn)
        MiClase = Nothing
    End Function
    Public Function objPerUbigeo(ByVal PerCodigo As String, ByVal MyTrans As SqlTransaction, ByVal Cn As SqlConnection) As SqlDataReader
        Dim Sql As String = "SELECT Persona.cPerCodigo, cPerApellido + ' ' + cPerNombre as Nombre, Persona.nPerTipo, " & _
                            "Departamento.cUbiGeoDescripcion AS Departamento, Provincia.cUbiGeoDescripcion AS Provincia, " & _
                            "Distrito.cUbiGeoDescripcion AS Distrito " & _
                            "FROM Persona inner JOIN " & _
                            "UbicacionGeografica Distrito ON '3' + SUBSTRING(Persona.cUbiGeoCodigo, 2, 6) = " & _
                            "LEFT(Distrito.cUbiGeoCodigo, 7) LEFT OUTER JOIN " & _
                            "UbicacionGeografica Provincia ON '2' + SUBSTRING(Persona.cUbiGeoCodigo, 2, 4) = " & _
                            "LEFT(Provincia.cUbiGeoCodigo, 5) LEFT OUTER JOIN " & _
                            "UbicacionGeografica Departamento ON '1' + SUBSTRING(Persona.cUbiGeoCodigo, 2, 2) = " & _
                            "LEFT(Departamento.cUbiGeoCodigo, 3) " & _
                            "where cPerCodigo = '" & PerCodigo & "'"
        Dim MiClase As New clsManejadorDatos
        Return MiClase.objExecuteReader(Sql, MyTrans, Cn)
        MiClase = Nothing
    End Function
    Public Function objPerDireccion(ByVal PerCodigo As String, ByVal MyTrans As SqlTransaction, ByVal Cn As SqlConnection) As SqlDataReader
        Dim Sql As String = "SELECT PerDomicilio.cPerDomDireccion, PerDomicilio.cUbiGeoCodigo, " & _
                            "RTRIM(UbicacionGeografica.cUbiGeoDescripcion) AS cUbiGeoDescripcion, " & _
                            "PerDomicilio.nPerDomStatus, Status.cConDescripcion AS Estado, " & _
                            "PerDomicilio.nPerDomCarta FROM PerDomicilio LEFT OUTER JOIN " & _
                            "Constante Status ON PerDomicilio.nPerDomStatus = Status.nConValor AND " & _
                            "Status.nConCodigo = 1008 LEFT OUTER JOIN UbicacionGeografica ON " & _
                            "PerDomicilio.cUbiGeoCodigo = UbicacionGeografica.cUbiGeoCodigo WHERE " & _
                            "PerDomicilio.cPerCodigo = '" & PerCodigo & "' ORDER BY ISNULL(PerDomicilio.nPerDomStatus,0)"
        Dim MiClase As New clsManejadorDatos
        Return MiClase.objExecuteReader(Sql, MyTrans, Cn)
        MiClase = Nothing
    End Function
    Public Function objPerTelefono(ByVal PerCodigo As String, ByVal MyTrans As SqlTransaction, ByVal Cn As SqlConnection) As SqlDataReader
        Dim sql As String = "SELECT PerTelefono.nPerTelTipo, Tipo.cConDescripcion, " & _
                            "PerTelefono.cPerTelNumero, PerTelefono.nPerTelStatus, Status.cConDescripcion AS Estado " & _
                            "FROM PerTelefono LEFT OUTER JOIN Constante Status ON " & _
                            "PerTelefono.nPerTelStatus = Status.nConValor AND Status.nConCodigo = 1008 " & _
                            "LEFT OUTER JOIN Constante Tipo ON PerTelefono.nPerTelTipo = " & _
                            "Tipo.nConValor AND Tipo.nConCodigo = 1003 WHERE PerTelefono.cPerCodigo = '" & PerCodigo & "'"
        Dim MiClase As New clsManejadorDatos
        Return MiClase.objExecuteReader(sql, MyTrans, Cn)
        MiClase = Nothing
    End Function
    Public Function objPerMail(ByVal PerCodigo As String, ByVal MyTrans As SqlTransaction, ByVal Cn As SqlConnection) As SqlDataReader
        Dim Sql As String = "SELECT cPerMaiNombre FROM PerMail WHERE cPerCodigo = '" & PerCodigo & "'"
        Dim MiClase As New clsManejadorDatos
        Return MiClase.objExecuteReader(Sql, MyTrans, Cn)
        MiClase = Nothing
    End Function
    Public Function Get_Asistente_By_Evento(ByVal cDocRefCodigo As String, ByVal cPerCodigo As String, _
                                    ByVal MyTrans As SqlTransaction, ByVal Cn As SqlConnection) As SqlDataReader

        Dim SQL As String = "SELECT DR.cDocRefCodigo, J.cPerCodigo AS cColCodigo, J.Colegio, DR.cDocCodigo, " & _
                            "DP.cPerCodigo, P.cPerApellido + ' ' +  P.cPerNombre, Uni.cUniOrgAbrev, ISNULL((SELECT cConDescripcion FROM dbo.Constante " & _
                            "WHERE (nConCodigo = 9500) AND (nConValor = DP.nDocTipo)), N'') AS Puesto, D.cDocObserv, ISNULL " & _
                            "((SELECT cDocNDoc FROM dbo.DocIdentifica AS DI " & _
                            "WHERE (cDocCodigo = D.cDocCodigo) AND (DP.cDocCodigo = cDocCodigo)), N'') AS Numero, ISNULL " & _
                            "((SELECT TOP (1) C.cConDescripcion FROM dbo.DocTratamiento AS Dt INNER JOIN " & _
                            "dbo.Constante AS C ON Dt.nEleCodigo = C.nConCodigo AND Dt.nCarCodigo = C.nConValor " & _
                            "WHERE (Dt.cDocCodigo = D.cDocCodigo) AND (DP.cDocCodigo = Dt.cDocCodigo) AND " & _
                            "(Dt.nEleCodigo = 9503)), N'') AS Catego, ISNULL ((SELECT TOP (1) 'x' AS Expr1 " & _
                            "FROM dbo.RegAsistencia AS RA " & _
                            "WHERE (cDocCodigo = DR.cDocRefCodigo) AND (cDocAcdCodigo = D.cDocCodigo) AND " & _
                            "(DR.cDocCodigo = cDocAcdCodigo) AND (nItem = 231709)), N'') AS ASist, DP.nDocTipo, DP.nPerRelacion " & _
                            "FROM dbo.DocRef AS DR INNER JOIN " & _
                            "dbo.Documentos AS D ON DR.cDocCodigo = D.cDocCodigo INNER JOIN " & _
                            "dbo.DocPersona AS DP ON D.cDocCodigo = DP.cDocCodigo INNER JOIN " & _
                            "dbo.Persona AS P ON DP.cPerCodigo = P.cPerCodigo INNER JOIN " & _
                            "(SELECT DP.cDocCodigo, DP.cPerCodigo, RTRIM(C.cPerApellido + ' ' + C.cPerNombre) AS Colegio " & _
                            "FROM dbo.DocPersona AS DP INNER JOIN " & _
                            "dbo.Persona AS C ON DP.cPerCodigo = C.cPerCodigo " & _
                            "WHERE (DP.nDocPerTipo = 1013)) AS J ON D.cDocCodigo = J.cDocCodigo LEFT OUTER JOIN " & _
                            "(SELECT Du.nDocUniOrgTipo, Uo.cUniOrgAbrev, Du.cDocCodigo " & _
                            "FROM dbo.DocUniOrg AS Du INNER JOIN " & _
                            "dbo.UnidadOrganizacional AS Uo ON Du.nUniOrgCodigo = Uo.nUniOrgCodigo " & _
                            "WHERE (Du.nDocUniOrgTipo = 1011)) AS Uni ON D.cDocCodigo = Uni.cDocCodigo " & _
                            "WHERE (DP.nDocPerTipo = 1011) AND (D.nDocTipo = 9555555) AND " & _
                            "(DR.cDocRefCodigo = '" & cDocRefCodigo & "') AND (DP.cPerCodigo in('" & cPerCodigo & "')) " & _
                            "ORDER BY P.cPerApellido"
        '200706110814531302

        Dim MiClase As New clsManejadorDatos
        Return MiClase.objExecuteReader(SQL, MyTrans, Cn)
        MiClase = Nothing
    End Function
    Public Function Get_Confirmacion_Asistencia_By_Actividad(ByVal nItem As Integer, ByVal cDocAcdCodigo As String, _
                    ByVal cDocCodigo As String, ByVal nEstado As Integer, ByVal MyTrans As SqlTransaction, ByVal Cn As SqlConnection) As SqlDataReader

        Dim SQL As String = "Select * From  RegAsistencia Where nItem = " & nItem & " AND cDocAcdCodigo = '" & cDocAcdCodigo & "' " & _
                            "AND cDocCodigo ='" & cDocCodigo & "' AND nEstado = " & nEstado & ""

        Dim MiClase As New clsManejadorDatos
        Return MiClase.objExecuteReader(SQL, MyTrans, Cn)
        MiClase = Nothing
    End Function
    Public Function Get_BusAlumno(ByVal PerApellido As String, ByVal PerAluRegEstado As Integer, ByVal MyTRans As SqlTransaction, _
                                    ByVal Cn As SqlConnection) As SqlDataReader

        Dim SQL As String = "Select P.cPerCodigo as Codigo, (P.cPerApellido + ' ' + P.cPerNombre) as Alumno PAR.nUniOrgCodigo From Persona P " & _
                            "Inner Join PerAluRegistra PAR on PAR.cPerCodigo = P.cPerCOdigo " & _
                            "Where cPerApellido Like '%" & PerApellido & "%' AND PAR.nPerAluRegEstado = " & PerAluRegEstado & ""


        Dim MiClase As New clsManejadorDatos
        Return MiClase.objExecuteReader(SQL, MyTRans, Cn)
        MiClase = Nothing

    End Function
End Class